package com.hsnn.medstgmini.base.dict.dao.impl;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.DicDrugnameDao;
import com.hsnn.medstgmini.base.dict.model.DicDrugname;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.util.Pagination;


/**
 * @category 通用名DAO
 * @author 郦东飞
 * @date 2015年11月10日
 */
@Repository
public class DicDrugnameDaoImpl extends BaseHibernateDAO implements DicDrugnameDao {
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil dao;
	

	/**
	 * 
	 *@category 获取通用名列表查询
	 *@author lidongfei
	 *@date 2015年11月10日
	 *Pagination
	 */
	@Override
	public Pagination getDicDrugnameList(
			Pagination page) {
		String drugnameCode = page.getConditions().get("drugnameCode");
		String drugName = page.getConditions().get("drugName");
		String status = page.getConditions().get("status");
		StringBuffer hql = new StringBuffer();
		hql.append(" from DicDrugname where 1 = 1 ");
		if(StringUtils.isNotBlank(drugnameCode)){
			hql.append(" and drugname_code = :drugnameCode ");
		} 
		if(StringUtils.isNotBlank(drugName)){
			hql.append(" and drug_name like :drugName ");
		}
		if(StringUtils.isNotBlank(status)){
			hql.append(" and status = :status ");
		}
		Query query = getSession().createQuery(hql.toString());
		StringBuffer hqlCount = new StringBuffer();
		hqlCount.append(" select count(*) ").append(hql);
		Query queryCount = getSession().createQuery(hqlCount.toString());
		if(StringUtils.isNotBlank(drugnameCode)){
			query.setString("drugnameCode", drugnameCode);
			queryCount.setString("drugnameCode", drugnameCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			query.setString("drugName", "%"+drugName+"%");
			queryCount.setString("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(status)){
			query.setString("status", status);
			queryCount.setString("status", status);
		}
		page.setRecords((Long)queryCount.uniqueResult());
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}
	
	
	/**
	 * 
	 *@category 启用和停用
	 *@author lidongfei
	 *@date 2015年11月10日
	 *Pagination
	 */
	@SuppressWarnings("unchecked")
	@Override
	public int changeStatus(DicDrugname dicDrugname) {
		Session session = sessionFactory.getCurrentSession();
		List<String> list = new ArrayList<String>();
		if("0".equals(dicDrugname.getStatus()+"")){
			StringBuffer sb = new StringBuffer()
			.append("select drugname_code from dic_drugname where drugname_code= :drugnameCode or status <> 0  ");
			Query query = session.createSQLQuery(sb.toString());
			query.setString("drugnameCode", dicDrugname.getDrugnameCode());
			List<Object> data = query.list();
			for(int i = 0;i<data.size();i++){
				list.add(data.get(i).toString());
			}
		}
		StringBuffer sql = new StringBuffer();
		sql.append("update dic_drugname  set ")
		  .append(" status= :status ,upd_datetime= :updDatetime ,upd_user= :updUser  ")
		  .append(" where drugname_code = :drugnameCode ");
		Query update = session.createSQLQuery(sql.toString());
		update.setString("status",dicDrugname.getStatus());
		update.setString("updUser", dicDrugname.getUpdUser());
		update.setTimestamp("updDatetime", new Date());
		update.setString("drugnameCode", dicDrugname.getDrugnameCode());
		int count = update.executeUpdate();
		session.flush();
		writeLogs(dicDrugname.getStatus()+"",dicDrugname.getDrugnameCode(),list,"");
		return count;
	}
	/**
	 * 
	 *@category 写日志
	 *@author 郦东飞
	 *@date 2015年11月10日
	 *Pagination
	 */
	@SuppressWarnings("unchecked")
	@Override
	public void writeLogs(String status, String id,List<String> idList,String drugname){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer detail = new StringBuffer();
		if(!"0".equals(status)){
			StringBuffer sb = new StringBuffer()
			.append(" select  drugname_code drugnameCode, drug_name drugName,status from dic_drugname where drugname_code = :id   ")
			.append(" union all ")
			.append(" select t.* from (select  drugname_code drugnameCode,drug_name drugName,status from dic_drugname_log where drugname_code = :id order by upd_datetime desc limit 1) t");
			Query query1 = session.createSQLQuery(sb.toString());
			query1.setString("id", id);
			query1.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List<Map<String,Object>> list = query1.list();
			if(list.size()==1){
				detail.append("通用名编码,")
			      	  .append(list.get(0).get("drugnameCode").toString())
					  .append(";通用名名称,")
				      .append(list.get(0).get("drugName").toString())
				      .append(";状态,启用");
			}else{
				if(!list.get(0).get("drugnameCode").toString().equals(list.get(1).get("drugnameCode").toString())){
					detail.append("通用名编码,")
					      .append(list.get(0).get("drugnameCode").toString())
					      .append(",")
					      .append(list.get(1).get("drugnameCode").toString());
				}
				if(!list.get(0).get("drugName").toString().equals(list.get(1).get("drugName").toString())&& StringUtils.isNotBlank(drugname)){
					detail.append(";通用名名称,")
					      .append(list.get(0).get("drugName").toString())
					      .append(",")
					      .append(list.get(1).get("drugName").toString());
				}
				if(!list.get(0).get("status").toString().equals(list.get(1).get("status").toString())){
					detail.append(";状态,")
					      .append("1".equals(list.get(0).get("status").toString())?"启用":"停用")
					      .append(",")
					      .append("1".equals(list.get(1).get("status").toString())?"启用":"停用");
				}
				detail.delete(0, 1);
			}
			
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into dic_drugname_log(log_id,drugname_code,drug_name,  ")
		   .append(" status,upd_datetime,upd_user,sort_id,detail) select ")
		   .append(" UUID(),t.*,:detail from dic_drugname t where t.drugname_code= :id ");
		if("0".equals(status)){
			sql.append(" or t.drugname_code in (:idList)");
			//字段名,修改后数据,修改前数据
			detail.append("状态,停用,启用");
		}
		
		Query query=session.createSQLQuery(sql.toString());
		query.setString("id",id);
		if("0".equals(status)){
			query.setParameterList("idList", idList);
		}
		query.setString("detail", detail.toString());
		query.executeUpdate();
		session.flush();
	}
	
	/**
	 * 
	 *@category 新增和编辑
	 *@author wangbing
	 *@date 2015年7月27日
	 *Pagination
	 */
	@Override
	public void addAndEdit(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String drugnameCode = page.getConditions().get("drugnameCode");
		String drugName = page.getConditions().get("drugName");
		String updUser = page.getConditions().get("updUser");
//		if(StringUtils.isNotBlank(drugnameCode)){
//		StringBuffer sql = new StringBuffer();
//		sql.append(" update dic_drugname set  drugname_code = :drugnameCode, drug_name = :drugName, ")
//		   .append(" upd_datetime = :updDatetime, upd_user = :updUser ")
//		   .append(" where drugname_code = :drugnameCode ");
//		Query query = session.createSQLQuery(sql.toString());
//		query.setString("drugnameCode",drugnameCode );
//		query.setString("drugName", drugName);
//		query.setTimestamp("updDatetime", new Date());
//		query.setString("updUser",updUser );
//		query.executeUpdate();
//		session.flush();
//		writeLogs("1",drugnameCode,null);
//		}else{ 
			
			StringBuffer sql = new StringBuffer();
			sql.append(" insert into dic_drugname (drugname_code,drug_name,status,upd_datetime,upd_user) values ")
			   .append("  (:uuid,:drugName,1, :updDatetime,:updUser )  ");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("uuid", drugnameCode);
			query.setString("drugName",drugName );
			query.setTimestamp("updDatetime", new Date());
			query.setString("updUser",updUser );
			query.executeUpdate();
			session.flush();
	
			writeLogs("1",drugnameCode,null,drugName);
//		}
	}
	@Override
	public void edit(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String drugnameCode = page.getConditions().get("drugnameCode");
		String drugName = page.getConditions().get("drugName");
		String updUser = page.getConditions().get("updUser");
	StringBuffer sql = new StringBuffer();
	sql.append(" update dic_drugname set   drug_name = :drugName, ")
		   .append(" upd_datetime = :updDatetime, upd_user = :updUser ")
		   .append(" where drugname_code = :drugnameCode ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("drugnameCode",drugnameCode );
		query.setString("drugName", drugName);
		query.setTimestamp("updDatetime", new Date());
		query.setString("updUser",updUser );
		query.executeUpdate();
		session.flush();
       writeLogs("1",drugnameCode,null,drugName); 			
	}

	/**
	 * 根据code或name查看有无重复数据
	 * @param code,name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicDrugname> getDrugnameBy(String key,String value) {
		StringBuffer hql = new StringBuffer();
		hql.append("from DicDrugname where ")
		   .append(key)
		   .append(" = ? ");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter(0, value);
		return query.list();
	}
	
	
	/**
	 * 
	 *@category 写日志（新增、编辑、启用、停用 操作后将操作记录写入日志维护记录）
	 *@author lidongfei
	 *@date 2015年11月10日
	 *Pagination
	 */
	@Override
	public void writeLog(String drugnameCode){
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_drugname_log(log_id,drugname_code,drug_name,  ")
		   .append("status,upd_datetime,upd_user,sort_id) select ")
		   .append(" UUID(),t.* from dic_drugname t where t.drugname_code= :drugnameCode ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("drugnameCode", drugnameCode);
		query.executeUpdate();
		session.flush();
		
	}
	
	
	

	/**
	 * 基础库 数据字典 材质字典 根据id查询数据
	 * @param Id
	 * @return
	 */
	@Override
	public DicDrugname getDicDrugnameByCode(String Code) {
		StringBuffer hql = new StringBuffer();
		hql.append("from DicDrugname where drugname_code = ?");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter(0, Code);
		return (DicDrugname) query.uniqueResult();
	}
	
	@Override
	public void exportExcel(Pagination page,OutputStream os){
        HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("通用名");  
        HSSFRow row = sheet.createRow((int) 0);  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        
        HSSFCell cell = null;
        String[] headers = {"通用名编码","通用名","状态"};
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Object> list = getFormNoPage(page);
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Object[] obj = (Object[]) list.get(i);  
            for(int j = 0;j<obj.length;j++){
            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
            }
            
        }  
        try {   
            //OutputStream os = new FileOutputStream("D:/Drugname.xls");  
            wb.write(os);  
            os.close();  
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
            e.printStackTrace();  
        } 
       
	}
	@SuppressWarnings("unchecked")
	private List<Object> getFormNoPage(Pagination page){
		String drugnameCode = page.getConditions().get("drugnameCode");
		String drugName = page.getConditions().get("drugName");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer()
		.append(" select  drugname_code drugnameCode, drug_name drugName,case status when 0 then '停用' else '启用' end   ")
		.append(" from dic_drugname where 1=1  ");
		
		if (StringUtils.isNotBlank(drugnameCode)) {
			sql.append(" and drugname_code = :drugnameCode ");
		}
		if (StringUtils.isNotBlank(drugName)) {
			sql.append(" and drug_name like :drugName ");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" and status = :status ");
		}

		Query query = getSession().createSQLQuery(sql.toString());
		if (StringUtils.isNotBlank(drugnameCode)) {
			query.setParameter("drugnameCode",  drugnameCode );
		}
		if (StringUtils.isNotBlank(drugName)) {
			query.setParameter("drugName", "%" + drugName + "%");
		}
		if (StringUtils.isNotBlank(status)) {
			query.setInteger("status", Integer.valueOf(status));
		}
		return query.list();
	}




	
	
	
	
	
	
	

}
